/*
	exec sp_board_kit_inventory_proc '0','0','All Vendors','0','0','0','XLsREPORT','0'
SELECT * from lot_table lt, product p, process_step ps where lt.processstep_id = p.processstep_id and p.processstep_id = ps.processstep_id and ( p_type = 'board' or p_type = 'kit')
*/
ALTER PROC sp_board_kit_inventory_proc

@PROCESS_TYPE	VARCHAR(50),
@PRODUCT_ID 	VARCHAR(50),
@VENDOR		VARCHAR(50),
@ASOFDATE	VARCHAR(50),
@LOT_CATEGORY	VARCHAR(50),
@LOT_STATUS	VARCHAR(50),
@REPORT_TYPE	VARCHAR(50),
@DEFAULT_PROMPT	VARCHAR(50)

AS
BEGIN
	CREATE TABLE #BOARD_KIT_INVENTORY_TABLE (
		[LOT ID] INT,[PROCESSSTEP ID] INT,[LOT NUMBER] VARCHAR(50),[FAB_LOT_NUMBER] VARCHAR(50),
		[LOT MANUAL HOLD] INT,[LOT CATEGORY] VARCHAR(50),[QTY IN] INT,[DATE IN] DATETIME,
		[LOT STATUS] VARCHAR(50),[DATE CODE] VARCHAR(50),[SHOP ORDER NUMBER] VARCHAR(50),
		[PO NUMBER] VARCHAR(50),[PART NUMBER] VARCHAR(50),[PARENT PART NUMBER] VARCHAR(50),
		[PRODUCT_TYPE] VARCHAR(50),[MARKETING PART NUMBER] VARCHAR(50),[PS TYPE] VARCHAR(50),
		[PS_TYPE_ID] INT,[VENDOR NAME] VARCHAR(50) )

	DECLARE @FGSQL_STRING	NVARCHAR(4000)
	DECLARE @SQL_STRING	NVARCHAR(4000)

	DECLARE @TEMP_VARCHAR 	VARCHAR(50)
	DECLARE @START_DATE 	DATETIME
	DECLARE @END_DATE 	DATETIME
	DECLARE @ASOFDATE_QTR	INT

	SET @FGSQL_STRING = N'
	INSERT INTO #BOARD_KIT_INVENTORY_TABLE
	SELECT 
		LT.LOT_ID,LT.PROCESSSTEP_ID,LOT_NUMBER,FAB_LOT_NUMBER,
		LOT_MANUAL_HOLD,LOT_CATEGORY,ISNULL(IL_QTY_BALANCE,IL_QTY_IN),DATE_IN,
		(CASE 
			WHEN LOT_CURRENT_STATUS = ''RUNNING'' THEN ''ACTIVE''
			ELSE ''HOLD''
		END),LOT_DATE_CODE,SHOP_ORDER_NUMBER,
		PO_NUMBER,PART_NUMBER,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
					END 
		),P_TYPE,P_MARKETING_PART_NUMBER,PS_TYPE,4,L_VENDOR_NAME '

	IF ( @ASOFDATE <> '0' )
	BEGIN
		--NEED TO CONFORM AND KEEP HISTORY FOR INVENTORY LOT
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)

		SET @FGSQL_STRING = @FGSQL_STRING + '
		FROM 
			--INVENTORY_LOT IL,LOT_TABLE_HISTORY LT,PRODUCT P,PROCESS_STEP PS,LOCATION L
			INVENTORY_LOT IL,LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS,LOCATION L
		WHERE
			IL.LOT_ID = LT.LOT_ID
			AND LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LT.LOCATION_ID = L.LOCATION_ID
			AND ( P_TYPE = ''BOARD'' OR P_TYPE = ''KIT'' )
			AND PS_TYPE = ''Finished Goods'' '
			--AND LT.TimeStamp = ( SELECT MAX(LTH.Timestamp) FROM LOT_TABLE_HISTORY LTH WHERE LTH.LOT_ID = LT.LOT_ID AND LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') '
	END
	ELSE
	BEGIN
		SET @FGSQL_STRING = @FGSQL_STRING + '
		FROM 
			INVENTORY_LOT IL,LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS,LOCATION L
		WHERE
			IL.LOT_ID = LT.LOT_ID
			and LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LT.LOCATION_ID = L.LOCATION_ID
			AND ( P_TYPE = ''BOARD'' OR P_TYPE = ''KIT'' )
			AND PS_TYPE = ''Finished Goods''  '
	END
	
		--AND P.ISAPPROVED = 0

	IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
	BEGIN
		SET @FGSQL_STRING = @FGSQL_STRING + ' AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 

		IF @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID <> '0'
		BEGIN
			SET @FGSQL_STRING = @FGSQL_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCT_ID +'''))'
		END
	END
	/*ELSE
	BEGIN
		IF @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID <> '0'
		BEGIN
			SET @SQL_STRING = @SQL_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCT_ID +'''))'
		END
	END*/	

	IF @VENDOR IS NOT NULL AND @VENDOR <> 'All Vendors'
	BEGIN
		SET @FGSQL_STRING = @FGSQL_STRING + ' AND L.L_VENDOR_NAME = ''' +  @VENDOR + '''' 
	END 

	IF @LOT_CATEGORY IS NOT NULL AND @LOT_CATEGORY <> '0'
	BEGIN
		SET @FGSQL_STRING = @FGSQL_STRING + ' AND LT.LOT_CATEGORY = ''' +  @LOT_CATEGORY + '''' 
	END 

	IF @LOT_STATUS IS NOT NULL AND @LOT_STATUS <> '0'
	BEGIN
		SET @FGSQL_STRING = @FGSQL_STRING + ' AND LT.LOT_CURRENT_STATUS = ''' +  @LOT_STATUS + '''' 
	END 
	ELSE
	BEGIN
		SET @FGSQL_STRING = @FGSQL_STRING + ' AND LT.LOT_CURRENT_STATUS <> ''CLOSE'' '
	END

	
	SET @SQL_STRING = N'
	INSERT INTO #BOARD_KIT_INVENTORY_TABLE
	SELECT 
		LT.LOT_ID,LT.PROCESSSTEP_ID,LOT_NUMBER,FAB_LOT_NUMBER,
		LOT_MANUAL_HOLD,LOT_CATEGORY,QTY_IN,DATE_IN,
		(CASE 
			WHEN LOT_CURRENT_STATUS = ''RUNNING'' THEN ''ACTIVE''
			ELSE ''HOLD''
		END),LOT_DATE_CODE,SHOP_ORDER_NUMBER,
		PO_NUMBER,PART_NUMBER,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
					CASE 
						WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
						WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
					END 
		),P_TYPE,P_MARKETING_PART_NUMBER,PS_TYPE,
		(CASE 
			WHEN PS_TYPE = ''DIE BANK'' THEN 1
			WHEN PS_TYPE = ''ASSEMBLY'' THEN 2
			WHEN PS_TYPE = ''TEST'' THEN 3
		END),L_VENDOR_NAME '

	IF ( @ASOFDATE <> '0' )
	BEGIN
		--NEED TO CONFORM AND KEEP HISTORY FOR INVENTORY LOT
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 12:00AM'
		SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
		SET @TEMP_VARCHAR = NULL
		SET @TEMP_VARCHAR = CONVERT(VARCHAR, @ASOFDATE, 101)+' 11:59PM'
		SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)

		SET @SQL_STRING = @SQL_STRING + '
		FROM 
			LOT_TABLE_HISTORY LT,PRODUCT P,PROCESS_STEP PS,LOCATION L
			
		WHERE
			LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LT.LOCATION_ID = L.LOCATION_ID
			AND ( P_TYPE = ''BOARD'' OR P_TYPE = ''KIT'' )
			AND ( PS_TYPE = ''DIE BANK'' OR  PS_TYPE = ''ASSEMBLY'' OR PS_TYPE = ''TEST'' ) '
			--AND LT.TimeStamp = ( SELECT MAX(LTH.Timestamp) FROM LOT_TABLE_HISTORY LTH WHERE LTH.LOT_ID = LT.LOT_ID AND LTH.TIMESTAMP BETWEEN '''+ CONVERT(VARCHAR,@START_DATE)+''''+' AND '''+ CONVERT(VARCHAR,@END_DATE)+''') '
	END
	ELSE
	BEGIN
		SET @SQL_STRING = @SQL_STRING + '
		FROM 
			LOT_TABLE LT,PRODUCT P,PROCESS_STEP PS,LOCATION L
		WHERE
			LT.PROCESSSTEP_ID = P.PROCESSSTEP_ID
			AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID
			AND LT.LOCATION_ID = L.LOCATION_ID
			AND ( P_TYPE = ''BOARD'' OR P_TYPE = ''KIT'' )
			AND ( PS_TYPE = ''DIE BANK'' OR  PS_TYPE = ''ASSEMBLY'' OR PS_TYPE = ''TEST'' ) '
	END
	
		--AND P.ISAPPROVED = 0

	IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND PS.PS_TYPE = ''' +  @PROCESS_TYPE + '''' 

		IF @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID <> '0'
		BEGIN
			SET @SQL_STRING = @SQL_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCT_ID +'''))'
		END
	END
	/*ELSE
	BEGIN
		IF @PRODUCT_ID IS NOT NULL AND @PRODUCT_ID <> '0'
		BEGIN
			SET @SQL_STRING = @SQL_STRING + ' AND LT.PROCESSSTEP_ID IN ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING ('''+ @PRODUCT_ID +'''))'
		END
	END*/	

	IF @VENDOR IS NOT NULL AND @VENDOR <> 'All Vendors'
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND L.L_VENDOR_NAME = ''' +  @VENDOR + '''' 
	END 

	IF @LOT_CATEGORY IS NOT NULL AND @LOT_CATEGORY <> '0'
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND LT.LOT_CATEGORY = ''' +  @LOT_CATEGORY + '''' 
	END 

	IF @LOT_STATUS IS NOT NULL AND @LOT_STATUS <> '0'
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND LT.LOT_CURRENT_STATUS = ''' +  @LOT_STATUS + '''' 
	END 
	ELSE
	BEGIN
		SET @SQL_STRING = @SQL_STRING + ' AND LT.LOT_CURRENT_STATUS <> ''CLOSE'' '
	END
	
	--PRINT '@FGSQL_STRING :'+@FGSQL_STRING
	--PRINT '@SQL_STRING :'+@SQL_STRING

	IF ( @PROCESS_TYPE = 'FINISHED GOODS')
	BEGIN
		EXEC (@FGSQL_STRING)
	END
	ELSE IF ( @PROCESS_TYPE != 'FINISHED GOODS' and @PROCESS_TYPE != '0' )
	BEGIN
		EXEC (@SQL_STRING)
	END
	ELSE
	BEGIN
		EXEC (@SQL_STRING+@FGSQL_STRING)
	END

	IF @REPORT_TYPE = 'XLREPORT'
	BEGIN
		SELECT 
			[PARENT PART NUMBER],[PART NUMBER],[MARKETING PART NUMBER],[PRODUCT_TYPE],[VENDOR NAME],
			(CASE 
				WHEN [PS TYPE] = 'DIE BANK' THEN 'Empty Board'
				WHEN [PS TYPE] = 'ASSEMBLY' THEN 'Board Assembly'
				WHEN [PS TYPE] = 'TEST' THEN 'Test Assembly'
				ELSE [PS TYPE]
			END) as [PS TYPE],[LOT NUMBER],[FAB_LOT_NUMBER],
			[DATE IN], [QTY IN],[LOT CATEGORY],[LOT STATUS]
		FROM 
			#BOARD_KIT_INVENTORY_TABLE
		ORDER BY 
			[PRODUCT_TYPE],[PART NUMBER],[PS_TYPE_ID]
	END
	ELSE
	BEGIN
		SELECT 
			[LOT ID],[PROCESSSTEP ID],[LOT NUMBER],[FAB_LOT_NUMBER],
			[LOT MANUAL HOLD],[LOT CATEGORY],[QTY IN],[DATE IN],
			[LOT STATUS],[DATE CODE],[SHOP ORDER NUMBER],
			[PO NUMBER],[PART NUMBER],[PARENT PART NUMBER],
			[PRODUCT_TYPE],[MARKETING PART NUMBER],
			(CASE 
				WHEN [PS TYPE] = 'DIE BANK' THEN 'Empty Board'
				WHEN [PS TYPE] = 'ASSEMBLY' THEN 'Board Assembly'
				WHEN [PS TYPE] = 'TEST' THEN 'Test Assembly'
				ELSE [PS TYPE]
			END) as [PS TYPE],[PS_TYPE_ID],[VENDOR NAME]
		FROM 
			#BOARD_KIT_INVENTORY_TABLE
		ORDER BY
			[PRODUCT_TYPE],[PART NUMBER],[PS_TYPE_ID]
	END		
END


	